#include "usermysqls.h"
#include "ui_usermysqls.h"
#include"mainwindow.h"
#include"logrecord.h"
Q_GLOBAL_STATIC(UserMysqlS,theInstance);
UserMysqlS::UserMysqlS(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::UserMysqlS)
{
    ui->setupUi(this);
    this->setWindowTitle("数据库后台查看界面");
    this->setWindowIcon(QIcon(":/res/res/SQL.ico"));
    connect();
}

UserMysqlS::~UserMysqlS()
{
    delete ui;
}

UserMysqlS *UserMysqlS::instance()
{
    return theInstance;
}
void UserMysqlS::connect()
{

    database=QSqlDatabase::addDatabase("QMYSQL","main");//连接mysql数据库，连接名为main
    database.setDatabaseName("projectdata");//设置要连接的数据库名称
    database.setHostName("localhost");//设置主机名称
    database.setUserName("root");//设置用户名称
    database.setPassword("zxc20021102");//设置用户密码
    if(database.open())
    {
        LogRecord::instance()->writeLogRecord("系统数据库连接成功","系统","系统级");
        QMessageBox::information(nullptr,"linking","Connect MySQL succeed",QMessageBox::Yes);



    }
    else{
         LogRecord::instance()->writeLogRecord("系统数据库连接失败","系统","系统级");
         QMessageBox::information(nullptr,"linking","Connect MySQL failed",QMessageBox::Cancel);

    }










}

bool UserMysqlS::getUserData(QVector<UserInfo> &users)
{


    QSqlQueryModel sql;

    sql.setQuery("select *from userInfo",database);
    int row=sql.rowCount();
    if(row<0)
    {
        QMessageBox::information(nullptr,"SELECT","没有查询到相关记录",QMessageBox::Yes);
        return false;
    }
    qDebug()<<row;


    for(int i=0;i<row;i++)
    {
        QString accountNumber;
        QString userName;
        int userType;
        QString password;
        UserInfo demo;
        for(int j=0;j<5;j++)
        {
            QModelIndex index=sql.index(i,j);
            if(j==0)
                demo.id=sql.data(index).toInt();
            else if(j==1)
                demo.accountNumber=sql.data(index).toString();
            else if(j==2)
                demo.userName=sql.data(index).toString();
            else if(j==3)
                demo.userType=sql.data(index).toInt();
            else
                demo.userPassword=sql.data(index).toString();


        }

        qDebug()<<demo.id<<" "<<demo.accountNumber<<""<<demo.userName<<" "<<demo.userType<<" "<<demo.userPassword<<endl;
//        //清空上次的内容；
//        userName="";
//        userType=0;
//        password="";
         users.push_back(demo);





    }



return true;



}

bool UserMysqlS::saveUserData(UserInfo &user)
{

    QVector<UserInfo>demo;
    getUserData(demo);
    int count=demo.count();
    qDebug()<<"count->"<<count<<endl;
    // QStringList userNameList;
    QStringList accountNumberList;
    //for(int i=0;i<count;i++)
    //{
    //    userNameList.append(demo[i].userName);
    //    qDebug()<<userNameList[i];
    //}
    for(int i=0;i<count;i++)
    {
        accountNumberList.append(demo[i].accountNumber);
        qDebug()<<accountNumberList[i]<<" ";
    }
    //bool ok=userNameList.contains(user.userName);
    bool ok=accountNumberList.contains(user.accountNumber);
    if(!ok)
    {
        if(user.accountNumber!=""&&user.userName!=""&&user.userPassword!="")
        {
            QSqlQuery query(database);
            query.prepare("INSERT INTO userinfo (accountNumber,userName, userType, userPassword) "
                          "VALUES (:accountNumber,:userName, :userType, :userPassword)");
            query.bindValue(":accountNumber",user.accountNumber);
            query.bindValue(":userName", user.userName);
            query.bindValue(":userType", user.userType);
            query.bindValue(":userPassword", user.userPassword);
            if (!query.exec())
            {
                qDebug() << "插入数据失败：" <<query.lastError().text();
//                QMessageBox msgBox;
//                msgBox.setStandardButtons(QMessageBox::Yes );
//                msgBox.setWindowTitle("Tip");
//                msgBox.setText("用户注册失败！");
//                msgBox.button(QMessageBox::Yes)->setText("确定");
//                msgBox.exec();
//                QMessageBox::information(this,"tip",   "删除用户成功" );
                return false;

            }
            else
            {
                QString qstringUserType;
                MainWindow::QStringUserType(user.userType,qstringUserType);
                LogRecord::instance()->writeLogRecord("注册用户",user.userName,qstringUserType);
                qDebug() << "用户注册成功！";
                QMessageBox msgBox;
                msgBox.setStandardButtons(QMessageBox::Yes );
                msgBox.setWindowTitle("Tip");
                msgBox.setText("用户注册成功！");
                msgBox.button(QMessageBox::Yes)->setText("确定");
                msgBox.exec();
                return true;
            }



        }
        else
        {
            QMessageBox msgBox;
            msgBox.setStandardButtons(QMessageBox::Yes );
            msgBox.setWindowTitle("Tip");
            msgBox.setText("账号、用户名、和密码都不能为空！");
            msgBox.button(QMessageBox::Yes)->setText("确定");
            msgBox.exec();

            qDebug()<<"账号、用户名、和密码不能为空"  <<endl;
            return false;

        }

    }
    else
    {
        QMessageBox msgBox;
        msgBox.setStandardButtons(QMessageBox::Yes );
        msgBox.setWindowTitle("Tip");
        msgBox.setText("该账号已被注册！");
        msgBox.button(QMessageBox::Yes)->setText("确定");
        msgBox.exec();
        qDebug()<<"该账号已被注册！";
//        emit clearRegisterInformation();
        RegisteredUserInformation ::instance()->ClearLineEdit();
        return false;
    }





}

bool UserMysqlS::saveSensorData(sensorData &data)
{

    QSqlQuery query(database);
    query.prepare("INSERT INTO sensordata (Time, AirTemperature, AirHumidity,LightIntensity,CO2Concentration,SoilMoisture) "
                  "VALUES (:Time, :AirTemperature, :AirHumidity,:LightIntensity,:CO2Concentration,:SoilMoisture)");
    query.prepare("INSERT INTO sensorDataStand (Time, AirTemperature, AirHumidity,LightIntensity,CO2Concentration,SoilMoisture) "
                  "VALUES (:Time, :AirTemperature, :AirHumidity,:LightIntensity,:CO2Concentration,:SoilMoisture)");
    query.bindValue(":Time", data.Time);
    query.bindValue(":AirTemperature", data.AirTemperature);
    query.bindValue(":AirHumidity", data.AirHumidity);
    query.bindValue(":LightIntensity", data.LightIntensity);
    query.bindValue(":CO2Concentration", data.CO2Concentration);
    query.bindValue(":SoilMoisture", data.SoilMoisture);
    if (!query.exec())
    {
        qDebug() << "插入sensorData数据失败：" <<query.lastError().text();
        return false;

    }
    else
    {
        //qDebug() << "sensorData数据插入成功！";
        return true;
    }










}



bool UserMysqlS::deleteUserData(const int id)
{
    qDebug()<<"删除-----ok";
    bool deleteSuccess = false;

    // 使用事务确保操作原子性
    QSqlDatabase::database().transaction();
    QSqlQuery query(database);
    QString userName= byIdGetUserName(id);
    int userType=byIdGetUserType(id);
    query.prepare("DELETE FROM userinfo WHERE id = ?");
    //query.bindValue(":id", id);
    query.addBindValue(id);

    if (query.exec())
    {
        if (query.numRowsAffected() > 0)
        {
            QSqlDatabase::database().commit();
            deleteSuccess= true;
            qDebug()<<"删除成功mysql";
            QString qstringUserType="";
            MainWindow::QStringUserType(userType,qstringUserType);
            LogRecord::instance()->writeLogRecord("删除用户信息",userName,qstringUserType);
            return deleteSuccess;
        }
        else
        {
            QSqlDatabase::database().rollback();

            qDebug() << "未找到匹配的数据";
            return deleteSuccess;

        }
    }
    else
    {
        QSqlDatabase::database().rollback();
        qDebug() << "删除失败：" << query.lastError().text();
        return deleteSuccess;


    }

}

int UserMysqlS::changeUserInformation(const UserInfo &Data)
{
    QSqlQuery query(database);
    query .prepare("UPDATE userinfo SET UserName = :userName, userPassword = :userPassword WHERE id = :id");
    // 绑定参数值
    query.bindValue(":userName", Data.userName);
    query.bindValue(":userPassword", Data.userPassword);
    query.bindValue(":id", Data.id);

    // 执行更新
    if (!query.exec())
    {
        qDebug() << "更新失败：" << query.lastError().text();
        return -1;
    }

    // 检查是否实际影响了行（确保存在对应 id）
    if (query.numRowsAffected() <= 0)
    {
        qDebug() << "未找到 ID 为" << Data.id << "的记录";
        return 0;
    }
    QString qstringUserType;
    MainWindow::QStringUserType(Data.userType,qstringUserType);
    LogRecord::instance()->writeLogRecord("修改用户成功",Data.userName,qstringUserType);
    qDebug() << "更新成功！";
    return 1;

}

int UserMysqlS::getSensorThresholdParameters(int id, QString columnName)
{

      QSqlQuery query(database);
       QString sql = QString("SELECT `%1` FROM sensorthresholdparameters WHERE id = :id").arg(columnName);
       query.prepare(sql);
       query.bindValue(":id", id);

       if (!query.exec())
       {
           qDebug() << "查询失败：" << query.lastError().text();
           return -1;
       }

       if (query.next())
       {
           return query.value(columnName).toInt(); // 返回第colum 列数据（即 colum 对应的值）
       }
       else
       {
           qDebug() << "未找到 ID 为" << id << "的记录";
           return -1;
       }


}

bool UserMysqlS::setSensorThresholdParameters(int id, QString columnName,int num,QString whatHappend,int choice)
{


    // 1. 校验列名是否合法（关键防御措施！）
   QStringList validColumns = {"报警上限", "报警下限","上警发生","下警发生"}; // 定义合法的列名白名单
        if (!validColumns.contains(columnName)) {
            qDebug() << "Invalid column name:" << columnName;
            return false;
        }

    QString sql = QString("UPDATE sensorthresholdparameters SET `%1` = :value WHERE id = :id").arg(columnName);

       // 3. 执行参数化查询
       QSqlQuery query(database);
       query.prepare(sql);
     if(!choice)
     {
     query.bindValue(":value", num); // 新值
     }
     else
     {
      query.bindValue(":value", whatHappend); // 新值
     }


       query.bindValue(":id", id);      // 用户id

       // 4. 执行并检查结果
       if (!query.exec()) {
           qDebug() << "Update failed:" << query.lastError().text();
           return false;
       }
       return true;






}

int UserMysqlS::getPidParameters(int id, QString columnName)
{

      QSqlQuery query(database);
     QString sql = QString("SELECT `%1` FROM pidparameterset WHERE id = :id").arg(columnName);
     query.prepare(sql);
     query.bindValue(":id", id);

     if (!query.exec())
     {
         qDebug() << "查询失败：" << query.lastError().text();
         return -1;
     }

     if (query.next())
     {
         return query.value(columnName).toInt(); // 返回第colum 列数据（即 colum 对应的值）
     }
     else
     {
         qDebug() << "未找到 ID 为" << id << "的记录";
         return -1;
     }

}

bool UserMysqlS::setPidParameters(int id, QString columnName, int num)
{

    // 1. 校验列名是否合法（关键防御措施！）
      QStringList validColumns = {"SetpointValue", "OutputLimitsUpper","OutputLimitsLower","Kp","Ti","Td","Deadband","SamplingTime"}; // 定义合法的列名白名单
        if (!validColumns.contains(columnName)) {
            qDebug() << "Invalid column name:" << columnName;
            return false;
        }

    QString sql = QString("UPDATE pidparameterset SET `%1` = :value WHERE id = :id").arg(columnName);

       // 3. 执行参数化查询
       QSqlQuery query(database);
       query.prepare(sql);
       query.bindValue(":value", num); // 新值
       query.bindValue(":id", id);      // 用户id

       // 4. 执行并检查结果
       if (!query.exec()) {
           qDebug() << "Update failed:" << query.lastError().text();
           return false;
       }
       if(columnName=="SetpointValue")
           emit sendPidSetPointValueChanged(id-1);
       return true;

}

QSqlDatabase &UserMysqlS::getDataBase()
{
    return database;
}

bool UserMysqlS::getSensorThresholdData(QVector<SensorThresholdData> &Data)
{



       QSqlQuery query("select *FROM sensorthresholdparameters",database); // 替换为你的表名和字段

       if (!query.exec())
       {
           qWarning() << "查询失败：" << query.lastError().text();
           return false;
       }

       while (query.next()) {
           SensorThresholdData tempData;
           tempData.id = query.value("id").toInt();         // 使用列名获取数据
           tempData.sensorName = query.value("传感器").toString();
           tempData.alarmUpperLimit = query.value("报警上限").toInt();
           tempData.alarmLowerLimit = query.value("报警下限").toInt();
           tempData.alarmUpperLimitHappend=query.value("上警发生").toString();
           tempData.alarmLowerLimitHappend=query.value("下警发生").toString();;

           Data.append(tempData);
       }

     return true;






}

void UserMysqlS::test()
{
    QSqlQuery query(database);
    QDateTime currentTime = QDateTime::currentDateTime();
    QDateTime startTime = currentTime.addSecs(-60);
    query.prepare(" SELECT Time,AirTemperature,AirHumidity,LightIntensity,CO2Concentration,SoilMoisture " " FROM sensordatastand " " WHERE Time BETWEEN ? AND ? " " ORDER BY Time ASC ");
//    query.bindValue(0, 1); // Id=1
//    query.bindValue(1, startTime.toString("yyyy-MM-dd HH:mm:ss"));
//    query.bindValue(2, currentTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(currentTime.toString("yyyy-MM-dd HH:mm:ss"));
//    query.addBindValue(startTime);
//    query.addBindValue(currentTime);

    if (!query.exec()) {
        qDebug() << "查询失败:" << query.lastError().text();
        return;
    }
}

int UserMysqlS::getAdminCount()
{
    int adminCount=-1;
    // 执行查询
    QSqlQuery query(database);
    QString sql = "SELECT COUNT(*) FROM userinfo WHERE userType = 1";

    if (!query.exec(sql))
    {
        qDebug() << "查询执行失败：" << query.lastError().text();
        return adminCount;
    }

    // 获取结果
    if (query.next())
    {
        adminCount = query.value(0).toInt();
        return adminCount;
    }








}

bool UserMysqlS::getPidSetPointValue()
{
    extern QVector<int>SetPointValueS;
    QSqlQuery query(database);
    QString columnName = "SetpointValue";
    if (query.exec("SELECT " + columnName + " FROM pidparameterset"))
    {
        while (query.next())
        {
            bool ok;
            int setPointValue = query.value(0).toInt(&ok); // 转换为整数
            if (ok)
                SetPointValueS.push_back(setPointValue);
        }

    }
    else
    {
        qDebug() << "查询失败：" << query.lastError().text();
        return false;
    }

//    for(QVector<int>::iterator it=SetPointValueS.begin();it!=SetPointValueS.end();it++)
//        qDebug()<<"PID参数"<<*it<<endl;


    return true;




}

bool UserMysqlS::getSensorWarningTimes()
{
extern QVector<int>SensorWarningTimesVector;
QSqlQuery query(database);
QString columnName = "WarningTimes";
if (query.exec("SELECT " + columnName + " FROM sensorwarningtimes"))
{
    while (query.next())
    {
        bool ok;
        int sensorwarningtimes = query.value(0).toInt(&ok); // 转换为整数
        if (ok)
            SensorWarningTimesVector.push_back(sensorwarningtimes);
    }

}
else
{
    qDebug() << "查询失败：" << query.lastError().text();
    return false;
}

   for(QVector<int>::iterator it=SensorWarningTimesVector.begin();it!=SensorWarningTimesVector.end();it++)
        qDebug()<<"SensorWarningTimes"<<*it<<endl;
   return true;
}

bool UserMysqlS::updateSensorWarningTimes()
{

    // 假设 SensorWarningTimesVector是要更新的值，id是对应的行 ID
    extern QVector<int>SensorWarningTimesVector;
    QVector<int> id = {1, 2, 3, 4, 5};
    QSqlQuery query(database);
    query.prepare("UPDATE sensorwarningtimes SET WarningTimes = ? WHERE id = ?");
    database.transaction();
    for (int i = 0; i < id .size(); ++i) {
        query.addBindValue(SensorWarningTimesVector[i]);
        query.addBindValue(id[i]);
        if (!query.exec()) {
            qDebug() << "更新失败，ID:" << id[i]
                        << "错误：" << query.lastError().text();
            database.rollback();
            return false;
        }
    }

    if (!database.commit()) {
        qDebug() << "事务提交失败：" << database.lastError().text();
        database.rollback();
        return false;

    } else
    {
        qDebug() << "更新成功，影响行数：" << SensorWarningTimesVector.size();
        return true;
    }
}

bool UserMysqlS::getTimeCycleSets()
{
extern QVector<int>TimeCycleSets;
QSqlQuery query(database);
QString columnName = "TimeValue";
if (query.exec("SELECT " + columnName + " FROM timecyclesets"))
{
    while (query.next())
    {
        bool ok;
        int TimeCycle = query.value(0).toInt(&ok); // 转换为整数
        if (ok)
            TimeCycleSets.push_back(TimeCycle);
    }

}
else
{
    qDebug() << "查询失败：" << query.lastError().text();
    return false;
}

//    for(QVector<int>::iterator it=TimeCycleSets.begin();it!=TimeCycleSets.end();it++)
//                qDebug()<<"时间参数"<<*it<<endl;


return true;

}

bool UserMysqlS::updateTimeCycleSets(int id)
{

    // 假设TimeCycleSets是要更新的值，id是对应的行 ID
    extern QVector<int>TimeCycleSets;
    QSqlQuery query(database);
    QStringList listTip;
    listTip<<"DataCyle更新成功！"<<"FrequencyCyle更新成功！"<<"WarningCyle更新成功！"<<"SensorCyle更新成功！"<<"CurveCyle更新成功！";
    query.prepare("UPDATE timecyclesets SET TimeValue = ? WHERE id = ?");
    database.transaction();
    query.addBindValue(TimeCycleSets[id]);
    query.addBindValue(id+1);
    if (!query.exec())
    {
        qDebug() << "更新失败，ID:" << id+1
                 << "错误：" << query.lastError().text();
        database.rollback();
        return false;
    }

    if (!database.commit()) {
        qDebug() << "事务提交失败：" << database.lastError().text();
        database.rollback();
        return false;

    }
    else
    {
        qDebug() << "更新成功，更新第"<<id+1<<"行"<<endl;
        QMessageBox::information(this,"success",listTip[id]);
        return true;
    }


}

QString UserMysqlS::byIdGetUserName(const int id)
{
    QSqlQuery query(database);
    QString value="";
    query.prepare("SELECT userName FROM userinfo WHERE id = :id");
    query.bindValue(":id", id);
    if (query.exec() && query.next())
    {
        value = query.value(0).toString(); // 获取第一列的值
        qDebug() << "Value:" << value;
        return value;
    }
    else
    {
        qDebug() << "Query failed or no results";
        return value;
    }
}

int UserMysqlS::byIdGetUserType(const int id)
{
    QSqlQuery query(database);
    int value=0;
    query.prepare("SELECT userType FROM userinfo WHERE id = :id");
    query.bindValue(":id", id);
    if (query.exec() && query.next())
    {
        value = query.value(0).toInt(); // 获取第一列的值
        qDebug() << "Value:" << value;
        return value;
    }
    else
    {
        qDebug() << "Query failed or no results";
        return value;
    }
}

void UserMysqlS::on_tabWidget_tabBarClicked(int index)
{
//获取当前的页面index
    currentTableIndex=index;
    disPlayTabel(index);
}

void UserMysqlS::disPlayTabel(int index)
{
QStringList TabelNames;

TabelNames<<"userinfo"<<"sensordatastand"<<"timecyclesets"<<"sensorthresholdparameters"<<"pidparameterset"<<"sensorwarningtimes";
switch(index)
{
case 0:
{

    // 2. 创建模型并绑定表
         model[0] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[0]->setTable(TabelNames[0]);

         // 3. 设置列标题（字段名到中文的映射）
         model[0]->setHeaderData(0,Qt::Horizontal,"id");
         model[0]->setHeaderData(1, Qt::Horizontal, "用户账号");
         model[0]->setHeaderData(2, Qt::Horizontal, "用户名");
         model[0]->setHeaderData(3, Qt::Horizontal, "用户类型");
         model[0]->setHeaderData(4, Qt::Horizontal, "用户密码");


         // 4. 加载数据
         if (!model[0]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[0]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_0->setModel(model[0]);
         //tableView->setModel(model);
         ui->tableView_0->resizeColumnsToContents();//自适应
         ui->tableView_0->setSortingEnabled(true);//自动排序
         ui->tableView_0->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸

}
    break;
case 1:
{
    // 2. 创建模型并绑定表
         model[1] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[1]->setTable(TabelNames[1]);

         // 3. 设置列标题（字段名到中文的映射）
         model[1]->setHeaderData(0,Qt::Horizontal,"id");
         model[1]->setHeaderData(1, Qt::Horizontal, "传感器采集时间");
         model[1]->setHeaderData(2, Qt::Horizontal, "空气温度");
         model[1]->setHeaderData(3, Qt::Horizontal, "空气湿度");
         model[1]->setHeaderData(4, Qt::Horizontal, "光照强度");
         model[1]->setHeaderData(5, Qt::Horizontal, "CO2浓度");
         model[1]->setHeaderData(6, Qt::Horizontal, "土壤湿度");


         // 4. 加载数据
         if (!model[1]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[1]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_1->setModel(model[1]);
         //tableView->setModel(model);
         ui->tableView_1->resizeColumnsToContents();//自适应
         ui->tableView_1->setSortingEnabled(true);//自动排序
         ui->tableView_1->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸


}
    break;
case 2:
{
    // 2. 创建模型并绑定表
         model[2] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[2]->setTable(TabelNames[2]);

         // 3. 设置列标题（字段名到中文的映射）
         model[2]->setHeaderData(0,Qt::Horizontal,"id");
         model[2]->setHeaderData(1, Qt::Horizontal, "时间类型");
         model[2]->setHeaderData(2, Qt::Horizontal, "时间值");
         // 4. 加载数据
         if (!model[2]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[2]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_2->setModel(model[2]);
         //tableView->setModel(model);
         ui->tableView_2->resizeColumnsToContents();//自适应
         ui->tableView_2->setSortingEnabled(true);//自动排序
         ui->tableView_2->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸




}
    break;
case 3:
{

    // 2. 创建模型并绑定表
         model[3] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[3]->setTable(TabelNames[3]);

         // 3. 设置列标题（字段名到中文的映射）
         model[3]->setHeaderData(0,Qt::Horizontal,"id");
         model[3]->setHeaderData(1, Qt::Horizontal, "传感器");
         model[3]->setHeaderData(2, Qt::Horizontal, "报警上限");
         model[3]->setHeaderData(3, Qt::Horizontal, "报警下限");
         model[3]->setHeaderData(4, Qt::Horizontal, "上警发生");
         model[3]->setHeaderData(5, Qt::Horizontal, "下警发生");



         // 4. 加载数据
         if (!model[3]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[3]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_3->setModel(model[3]);
         //tableView->setModel(model);
         ui->tableView_3->resizeColumnsToContents();//自适应
         ui->tableView_3->setSortingEnabled(true);//自动排序
         ui->tableView_3->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸



}
    break;
case 4:
{

    // 2. 创建模型并绑定表
         model[4] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[4]->setTable(TabelNames[4]);

         // 3. 设置列标题（字段名到中文的映射）
         model[4]->setHeaderData(0,Qt::Horizontal,"id");
         model[4]->setHeaderData(1, Qt::Horizontal, "Pid控制名称");
         model[4]->setHeaderData(2, Qt::Horizontal, "Pid设定值");
         model[4]->setHeaderData(3, Qt::Horizontal, "上限输出值");
         model[4]->setHeaderData(4, Qt::Horizontal, "下限输出值");
         model[4]->setHeaderData(5, Qt::Horizontal, "比例系数");
         model[4]->setHeaderData(6, Qt::Horizontal, "积分时间");
         model[4]->setHeaderData(7, Qt::Horizontal, "微分时间");
         model[4]->setHeaderData(8, Qt::Horizontal, "死区范围");
         model[4]->setHeaderData(9, Qt::Horizontal, "采样时间");




         // 4. 加载数据
         if (!model[4]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[4]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_4->setModel(model[4]);
         //tableView->setModel(model);
         ui->tableView_4->resizeColumnsToContents();//自适应
         ui->tableView_4->setSortingEnabled(true);//自动排序
         ui->tableView_4->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸



}
    break;
case 5:
{
    // 2. 创建模型并绑定表
         model[5] = new QSqlTableModel(this,UserMysqlS::instance()->getDataBase());
         model[5]->setTable(TabelNames[5]);

         // 3. 设置列标题（字段名到中文的映射）
         model[5]->setHeaderData(0,Qt::Horizontal,"id");
         model[5]->setHeaderData(1, Qt::Horizontal, "传感器名称");
         model[5]->setHeaderData(2, Qt::Horizontal, "警告次数");



         // 4. 加载数据
         if (!model[5]->select()) {
             QMessageBox::critical(this, "错误", "查询失败: " + model[5]->lastError().text());
             //UserMysqlS::instance()->getDataBase()->close();
             return;
         }

         // 5. 创建视图并绑定模型
        // QTableView *tableView = new QTableView(this);
         ui->tableView_5->setModel(model[5]);
         //tableView->setModel(model);
         ui->tableView_5->resizeColumnsToContents();//自适应
         ui->tableView_5->setSortingEnabled(true);//自动排序
         ui->tableView_5->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);//可拉伸
}
    break;

}
}

void UserMysqlS::setCurrentTabelView(int index)
{
    ui->tabWidget->setCurrentIndex(index);
}

void UserMysqlS::setTabelViewEnabled(int index, bool status)
{
   ui->tabWidget->tabBar()->setTabEnabled(index, status);
}

void UserMysqlS::refreshTableView(int index)
{
    // 禁用按钮防止重复点击
        ui->pushButtonRefresh->setEnabled(false);
        QApplication::processEvents();  // 立即更新UI

        // 重新加载数据
        if (!model[index]->select()) {
            QMessageBox::critical(this, "错误", "刷新失败: " + model[index]->lastError().text());
        } else {
            // 滚动到最新数据（假设按时间降序排序）

            switch(index)
            {
             case 0:
                 ui->tableView_0->scrollToTop();
                break;
            case 1:
                ui->tableView_1->scrollToTop();
               break;
            case 2:
                ui->tableView_2->scrollToTop();
               break;
            case 3:
                ui->tableView_3->scrollToTop();
               break;
            case 4:
                ui->tableView_4->scrollToTop();
            case 5:
                ui->tableView_5->scrollToTop();
               break;

            }
        }

        // 恢复按钮状态
        ui->pushButtonRefresh->setEnabled(true);

}




void UserMysqlS::on_pushButtonBack_clicked()
{
   this->close();
}


void UserMysqlS::on_pushButtonRefresh_clicked()
{
refreshTableView(currentTableIndex);
}


void UserMysqlS:: exportTableViewToExcel(QTableView *tableView)
{
    QString fileName = QFileDialog::getSaveFileName(nullptr, "保存为 Excel 文件", "F:/Graduation_project/WorkPlace/work-place/Vegetable_greenhouse_control_system/excel/SQL", "Excel 文件 (*.xlsx)");

    if (fileName.isEmpty())
        return;

    QAbstractItemModel *model = tableView->model();
    if (!model)
        return;

    Document xlsx;

    int rowCount = model->rowCount();
    int colCount = model->columnCount();

    // 设置表头
    for (int col = 0; col < colCount; ++col) {
        QString header = model->headerData(col, Qt::Horizontal).toString();
        xlsx.write(1, col + 1, header);  // Excel 行列从 1 开始
    }

    // 写入内容
    for (int row = 0; row < rowCount; ++row) {
        for (int col = 0; col < colCount; ++col) {
            QVariant data = model->data(model->index(row, col));
            xlsx.write(row + 2, col + 1, data);
        }
    }

    // 保存文件
    xlsx.saveAs(fileName);
    QString saveTip="保存"+fileName+"成功";
    QMessageBox:: information(this,"Success!",saveTip);
}





void UserMysqlS::on_pushButtonExportExcel_clicked()
{

    int index=ui->tabWidget->currentIndex();
    switch(index)
    {
    case 0:
        exportTableViewToExcel(ui->tableView_0);
        break;
    case 1:
         exportTableViewToExcel(ui->tableView_1);
        break;
    case 2:
         exportTableViewToExcel(ui->tableView_2);
        break;
    case 3:
         exportTableViewToExcel(ui->tableView_3);
        break;
    case 4:
         exportTableViewToExcel(ui->tableView_4);
        break;
    case 5:
         exportTableViewToExcel(ui->tableView_5);
        break;
    }


}

